package com.epoint.BookManager.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.druid.util.StringUtils;
import com.epoint.BookManager.domain.BookInfo;
import com.epoint.BookManager.utils.JDBCUtil;
import com.epoint.BookManager.utils.StringUtil;
import com.epoint.BookManager.utils.UUIDStringUtils;

import java.sql.PreparedStatement;


public class BookInfoDao {
	
/**
 * 查询所有的数据
 * @param bookName
 * @param bookType
 * @param pageIndex
 * @param pageSize
 * @param sortField
 * @param sortOrder
 * @return
 */
public List<BookInfo> getBookList(String bookName, int bookType, int pageIndex, int pageSize, String sortField,
        String sortOrder) {

    Connection conn = JDBCUtil.getConnection();

    
    PreparedStatement pst = null;
    ResultSet rs = null;
    // 图书列表
    ArrayList<BookInfo> bookInfoList = new ArrayList<BookInfo>();
    ArrayList<Object> params = new ArrayList<>();
    StringBuffer buffer = new StringBuffer();

    buffer.append("select * from bookinfo where 1=1");



    if(StringUtil.isNotBlank(bookName)) {
    	buffer.append(" and bookname like concat('%',?,'%')");
    	params.add(bookName);
    }
    if(bookType != 0) {
    	buffer.append(" and booktype = ?");
    	params.add(bookType);
    }
	   if(!StringUtils.isEmpty(sortField)&&!StringUtils.isEmpty(sortOrder)){
	   	buffer.append(" order by"+sortField+" "+sortOrder);
	   }
	  
	   if(-1!=pageIndex&&-1!=pageSize) {
	 	buffer.append(" limit ? ,?");
	  	params.add(pageIndex*pageSize);
	   	params.add(pageSize);
	   }


    try {
        pst = conn.prepareStatement(buffer.toString());

        int sizeCount = params.size();
        for (int i = 0; i < sizeCount; i++) {
            if ((sizeCount - i) <= 3) {
            	pst.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
            }
            else {
            	pst.setString(i + 1, params.get(i).toString());
            }
        }

        rs = pst.executeQuery();
        while (rs.next()) {
            BookInfo bookInfo = new BookInfo();
            bookInfo.setBookid(rs.getString("bookid"));
            bookInfo.setBookname(rs.getString("bookname"));
            bookInfo.setPublisher(rs.getString("publisher"));
            bookInfo.setAuthor(rs.getString("author"));
            bookInfo.setBooktype(rs.getInt("booktype"));
            bookInfo.setRemain(rs.getInt("remain"));
            bookInfoList.add(bookInfo);
        }
    }
    catch (Exception e) {
        e.printStackTrace();
    }finally {
    	JDBCUtil.closeConnection(conn, pst, rs);
    }
    
    return bookInfoList;
}
/**
 * 添加数据的方法
 * @param bookInfo
 * @return
 */
public int addBookInfo(BookInfo bookInfo) {
	Connection conn = JDBCUtil.getConnection();
	int updateCount = 0;
	PreparedStatement pst = null;
	
	//判断是否有重复的书籍
	int i = checkIsRepeat(bookInfo.getBookname(),  bookInfo.getAuthor());
	if(i!=0) {
		return 0;
	}
	
	try {
		String sql = "insert into bookinfo (bookid,bookname,booktype,publisher,author,remain) values(?,?,?,?,?,?)";
		pst = conn.prepareStatement(sql);
		String bookid = UUIDStringUtils.randomUUID();
		pst.setString(1, bookid);
		pst.setString(2, bookInfo.getBookname());
		pst.setInt(3, bookInfo.getBooktype());
		pst.setString(4, bookInfo.getAuthor());
		pst.setString(5, bookInfo.getPublisher());
		pst.setInt(6, bookInfo.getRemain());
		updateCount = pst.executeUpdate();
	}catch (Exception e) {
		e.printStackTrace();
		// TODO: handle exception
	}
	finally {
		JDBCUtil.closeConnection(conn, pst, null);
	}
	return updateCount;
}


/**
 * 检查图书是否重复
 * @param bookName
 * @param author
 * @return
 */
public int checkIsRepeat(String bookName, String author) {
    Connection con = JDBCUtil.getConnection();
    PreparedStatement pStatement = null;
    String sql = "select count(*) from bookinfo where bookname=? and author=?";
    ResultSet resultSet = null;
    int i = 0;
    try {
        pStatement = con.prepareStatement(sql);
        pStatement.setString(1, bookName);
        pStatement.setString(2, author);
        resultSet = pStatement.executeQuery();
        while (resultSet.next()) {
            i = resultSet.getInt(1);
        }
    }
    catch (SQLException e) {
        e.printStackTrace();
    }
    finally {
        JDBCUtil.closeConnection(con, pStatement, resultSet);
    }
    return i;
}

/**
 * 修改图书
 * @param booktype
 * @param publisher
 * @param bookid
 * @return
 */
public int updateBookInfo(int booktype,String publisher,String bookid) {
    Connection con = JDBCUtil.getConnection();
    String sql = "update bookinfo set publisher=?,booktype=? where bookid=?";
    PreparedStatement ps = null;
    int i = 0;
    try {
        ps = con.prepareStatement(sql);
        ps.setString(1, publisher);
        ps.setInt(2, booktype);
        ps.setString(3, bookid);
        i = ps.executeUpdate();
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    finally {
        JDBCUtil.closeConnection(con, ps, null);
    }
    return i;
}

/**
 * 根据bookId删除book
 * @param bookId
 * @return
 * @throws SQLException
 */
public int deleteBookinfo(String bookId) throws SQLException {
    Connection con = JDBCUtil.getConnection();
    String sql = "delete from bookinfo where bookid = ?";
    PreparedStatement ps = null;
    int result = 0;
    try {
        ps = con.prepareStatement(sql);
        ps.setString(1, bookId);
    	result = ps.executeUpdate();
    	System.out.println("hellow");
    }catch (Exception e) {
		// TODO: handle exception
	}finally {
		JDBCUtil.closeConnection(con, ps, null);
	}
    return result;
}


/**
 * 根据bookid查询book
 * @param bookId
 * @return
 */
public BookInfo queryBookInfoByID(String bookId) {
    Connection con = JDBCUtil.getConnection();
    String sql = "select * from bookinfo where bookid = ?";
    PreparedStatement ps = null;
    BookInfo bookInfo = null;
    ResultSet rs = null;
    try {
        ps = con.prepareStatement(sql);
        ps.setString(1, bookId);
        rs = ps.executeQuery();
        while (rs.next()) {
            bookInfo = new BookInfo();
            bookInfo.setBookid(rs.getString("bookid"));
            bookInfo.setBookname(rs.getString("bookname"));
            bookInfo.setAuthor(rs.getString("author"));
            bookInfo.setPublisher(rs.getString("publisher"));
            bookInfo.setBooktype(rs.getInt("booktype"));
            bookInfo.setRemain(rs.getInt("remain"));
        }
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    finally {
        JDBCUtil.closeConnection(con, ps, rs);
    }
    return bookInfo;
}


/**
 * 查询可以借阅的书籍
 * @return
 */
	public List<BookInfo> CanListBychance() {
		Connection conn = JDBCUtil.getConnection();
	    PreparedStatement pst = null;
	    ResultSet rs = null;
	    // 图书列表
	    ArrayList<BookInfo> bookInfoList = new ArrayList<BookInfo>();
	    StringBuffer buffer = new StringBuffer();
	    buffer.append("select * from bookinfo where  remain>0");

	    try {
	        pst = conn.prepareStatement(buffer.toString());
	        rs = pst.executeQuery();
	        while (rs.next()) {
	            BookInfo bookInfo = new BookInfo();
	            bookInfo.setBookid(rs.getString("bookid"));
	            bookInfo.setBookname(rs.getString("bookname"));
	            bookInfoList.add(bookInfo);
	        }
	    }
	    catch (Exception e) {
	        e.printStackTrace();
	    }finally {
	    	JDBCUtil.closeConnection(conn, pst, rs);
	    }
	    
	    return bookInfoList;
	}

}	

